
IF EXISTS (
    SELECT
      1
    FROM sysobjects
    WHERE id = OBJECT_ID('sync_ClientPro_Card_add')
      AND type IN ('P', 'PC')
  )
  DROP PROCEDURE sync_ClientPro_Card_add

GO
create  procedure [dbo].[sync_ClientPro_Card_add] 	--发卡处理过程
(
@dev_serial char(7),		--发卡设备编号
@lx int = 0,					--过程类型（停用）
@ac_type char(16)=null,		--人员账户类型
@user_serial bigint,		--人员序号
@card_hao varchar(20),		--物理卡号
@jm_kh varchar(20),			--加密卡号
@card_serial char(8),		--卡顺序号
@card_lx int,				--卡类型
@card_work int,				--读写状态
@card_type int,				--卡状态
@ip  varchar(20),     		--操作IP
@gly_no nvarchar(50),		--操作员
@reg_serial varchar(50)		--企业编号
)
as
--返回值必须添加的内容
set nocount on

--变量定义
declare @ac_no char(16)			--账户编号
declare @rec_count int			--计数器
declare @card_order int			--卡号顺序
declare @xh int					--卡号表序号

declare @pxh varchar(8)      	 --卡父序号
declare @tt_form varchar(50)	 --卡转换格式
declare @tempkh varchar(50) 	 --转换后卡号

declare @tt_xh int          	 --卡转换格式序号
declare @tt_cut int				 --截取位数
declare @tt_hex int			 	 --进制
declare @tt_fields varchar(50)	 --

declare @user_no varchar(100)
declare @result int
declare @tmp_card varchar(50)
declare @module_card int
declare @card_serial_max varchar(50)
declare @count int
--变量赋值
set @rec_count=0
set @card_order=0

	---判断人员信息

    --1.人员是否有卡
    select @user_no = user_no from dt_user where user_serial = @user_serial
    select top 1 @tmp_card =(case when cast(user_serial as varchar(30))= isnull(user_card,'') then '' else  isnull(user_card,'') end) from dt_user where user_serial=@user_serial
	if @tmp_card <> ''
	begin

		select 2	--人员存在正在使用的卡片
		print '人员存在正在使用的卡片,工号:'+@user_no
		return

	end

	--2.新卡是否占用
	if (select count(card_hao) from dt_card where card_hao=@card_hao and card_type<>4)>0
	begin

		select 3	--新卡被占用
		print '新卡被占用,卡号:'+@card_hao
		return

	end

    	--3.创建人员账户//////建议去掉！！！！！！！！！！！
	exec ClientPro_user_kh @ac_type,@user_serial,@gly_no,@result output
	if @result <> 0
	begin
		print '开户失败,卡号:'+@card_hao
		return				--返回
	end;

	begin transaction
	--4.生成卡信息
	--计算Card_order

	select @card_order=(max(card_order)+1) from dt_card where user_serial=@user_serial

	--获取卡顺序号

	select @module_card = module_card+1 from wt_module where module_id='0009'
	set @card_serial = right('00000000'+convert(varchar(8),@module_card),8)  --右取8位 不足位前补0
	update wt_module set module_card=@module_card where module_id='0009'


	--.写卡片操作日志（未决状态）
	insert into wt_card_log(
	lx,log_type,log_state,user_serial,dev_serial,card_old,
	card_new,log_xh,log_sj,log_ip,gly_no,log_lx,log_group,
	log_row,log_erro,bz,regserial
	)values(
	12,0,0,@user_serial,@dev_serial,@card_hao,
	@card_serial,@card_lx,getdate(),@ip,@gly_no,0,null,
	null,0,null,@reg_serial
	)


	insert into dt_card(
	lx, gly_no, sj, user_serial, card_lx,
	card_type, card_hao, card_order, card_serial,
	loss_count, isCardReplace
	)values(
	0,@gly_no,getdate(),@user_serial,@card_lx,
	@card_type,@card_hao,@card_order,@card_Serial,
	0,0
	)
	set @xh=SCOPE_IDENTITY()

	--生成转换卡号格式
	exec ClientPro_Card_formate @xh,@card_lx

	--5.生成个人账户
	--计算Card_order
	select @card_order=(max(card_order)+1) from dt_ac_card where user_serial=@user_serial
	insert into dt_ac_card(
	user_serial,card_serial,card_hao,card_order,ac_money,ac_addm,ac_addo,ac_subm,
	ac_subo,ac_regm,ac_make,ac_eachm,ac_eacho,ac_state,sj,ks_sj,gly_no
	)VALUES(
	@user_serial,@card_serial,@card_hao,isnull(@card_order,1),0,0,0,0,
	0,0,0,0,0,0,getdate(),getdate(),@gly_no
	)


	--5.生成联机账户
	if (select count(1) from dt_ac_link where user_serial=@user_serial)=0
	begin
		insert into dt_ac_link(
		user_serial,card_serial,card_order,ac_money,ac_addm,ac_addo,ac_subm,
		ac_subo,ac_regm,ac_make,ac_eachm,ac_eacho,ac_state,sj,gly_no,card_hao
		)VALUES(
		@user_serial,@card_serial,@card_order,0,0,0,0,
		0,0,0,0,0,0,getdate(),'admin',@card_hao

		)
	end
	else
	begin

		update dt_ac_link set
		card_serial=@card_serial,
		card_hao=@card_hao
		where user_serial=@user_serial

	end

	--6.更新人员表状态
	update dt_user set user_ac = 1,user_card=@card_hao,user_sj=getdate()
	where user_serial = @user_serial


	--7.生成增量档案日志20151021
	insert into wt_public(lx,log_type,is_all,user_serial,card_serial,log_sj,log_ip,gly_no,new_number,old_number)
	values(8,4,0,@user_serial,@card_serial,getdate(),@ip,@gly_no,0,0)
	--insert into wt_user_up(lx,log_type,log_finger,user_serial,card_serial,log_sj,log_ip,gly_no)
	--values(1,1,0,@user_serial,@card_serial,getdate(),@ip,@gly_no)

	--8.新增照片日志
	--if exists (select 1 from dt_user where user_serial=@user_serial and user_photo=1)
	--begin
	--insert into wt_user_up(lx,log_type,log_finger,user_serial,card_serial,log_sj,log_ip,gly_no)
	--values(1,2,0,@user_serial,@card_serial,getdate(),@ip,@gly_no)
	--end



	--9.门禁员工密码增加(判断是否存在门禁表再插入)
	--if exists (select 1 from mj_pw_real where user_serial=@user_serial)
	--begin
	--	insert into wt_mj_ver(type,lx,log_sj,log_ip,gly_no,gate_bh,log_lx,log_bz,fx)
	--	values(0,2,getdate(),null,null,null,1,@user_serial,null)
	--end

	---更新发卡日志
	Update WT_CARD_LOG set lx=0,log_sj=getdate(),log_ip=@ip,gly_no=@gly_no,card_old=@card_hao
	where lx=12 and user_serial=@user_serial and card_new=@card_serial


	commit transaction


GO

